package sdfi.lee.DAO;



import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Session;
import org.hibernate.Transaction;

import sdfi.lee.Entity.Yiqishare;
import sdfi.lee.Properties.ReadXMLFromString;

public class YiqiDAO {
	public String addYiqi(Yiqishare yiqi)
	{
		try
		{
			Session session=DAO.openSession();
			Transaction ts=session.beginTransaction();
			session.save(yiqi);
			ts.commit();
			return "success";
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			return "error";
		}
		finally
		{
			DAO.closeSession();
		}
	}
	
	public List<Yiqishare> getAllYiqi(int pg,int size,String yiqiname,String userfor,String unit,String factory,String relation)
	{
		List<Yiqishare> list=new ArrayList<Yiqishare>();
		Session session=DAO.openSession();
		
		if("".equals(yiqiname)) yiqiname=null;
		if("".equals(userfor)) userfor=null;
		if("".equals(unit)) unit=null;
		if("".equals(factory)) factory=null;
		if("".equals(relation)) relation=null;
		boolean  hasParam=false;
		try
		{
			
			Connection con=session.connection();
			con.setAutoCommit(false);
			String sql="select *from answer as a ";
			if(yiqiname!=null)
			{
				if(!hasParam)
					sql+="where a.answer like '%"+yiqiname.trim()+"%' ";
				else sql+=" and a.answer like '%"+yiqiname.trim()+"%' ";
				hasParam=true;
				
			}
			if(userfor!=null)
			{
				if(!hasParam)
					sql+="where a.answer like '%"+userfor.trim()+"%' ";
				else sql+=" and a.answer like '%"+userfor.trim()+"%' ";
				hasParam=true;
			}
			if(unit!=null)
			{
				if(!hasParam)
					sql+="where a.answer like '%"+unit.trim()+"%' ";
				else sql+=" and a.answer like '%"+unit.trim()+"%' ";
				hasParam=true;
			}
			if(factory!=null)
			{
				if(!hasParam)
					sql+="where a.answer like '%"+factory.trim()+"%' ";
				else sql+=" and a.answer like '%"+factory.trim()+"%' ";
				hasParam=true;
			}
			if(relation!=null)
			{
				if(!hasParam)
					sql+="where a.answer like '%"+relation.trim()+"%' ";
				else sql+=" and a.answer like '%"+relation.trim()+"%' ";
				hasParam=true;
			}
			if(!hasParam) sql+="where a.type=1";
			else
			{
			sql+="and a.type=1";
			
			}
			sql+=" limit "+(pg-1)*size+","+size;
			;
			System.out.println(sql);
			long lt=System.currentTimeMillis();
			Statement ps=con.createStatement();
			ResultSet rs=ps.executeQuery(sql);
		
			while(rs.next())
			{
			
					ReadXMLFromString rxfs=new ReadXMLFromString(rs.getString("answer"));
					list.add(rxfs.readerYiqiFromString());
			}
			con.commit();
			return list;
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			
			return list;
		}
		finally
		{
			DAO.closeSession();
		}
	}
	
	public List<Yiqishare> getAllYiqiAll(int page,int size)
	{
		List<Yiqishare> list=new ArrayList<Yiqishare>();
		Session session=DAO.openSession();
		Connection con=session.connection();
		
		try
		{
			con.setAutoCommit(false);
			String sql="select answer from answer where type=1 limit "+(page-1)+","+size;
			
			ResultSet rs=con.createStatement().executeQuery(sql);
			
			while(rs.next())
			{ 
			
			ReadXMLFromString rxfs=new ReadXMLFromString();
				rxfs.setSource(rs.getString("answer"));
			
				Yiqishare yiqi=new Yiqishare();
				yiqi.setYiqiname(rxfs.readerValueByNode("/profile/@NE"));
				list.add(yiqi);
			}
			con.commit();
			return list;
		}
		catch(Exception ee)
		{
		ee.printStackTrace();
			return list;
		}
		finally
		{
			DAO.closeSession();
		}
	}
	public Yiqishare getYiqishareById(Integer id)
	{
		Yiqishare share=null;
		List<Yiqishare> list=new ArrayList<Yiqishare>();
		Session session=DAO.openSession();
		Transaction ts=null;
		
		try
		{
			ts=session.beginTransaction();
			share=(Yiqishare)session.get(Yiqishare.class, id);
			return share;
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			if(ts!=null)
				ts.rollback();
			return share;
		}
		finally
		{
			DAO.closeSession();
		}
		
	}

	public String deleteYiqishareBy(Integer id)
	{
		Session session=DAO.openSession();
		Transaction ts=null;
		
		try
		{
			ts=session.beginTransaction();
			Yiqishare share=(Yiqishare)session.get(Yiqishare.class, id);
			session.delete(share);
			ts.commit();
		}
		catch(Exception ee)
		{
			ee.printStackTrace();
			if(ts!=null)
				ts.rollback();
			return "error";
		}
		finally
		{
			DAO.closeSession();
		}return "success";
	}
}
